package com.luqili.utils.pub.sql;

import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ClassUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.handler.EntityHandler;
import cn.hutool.db.handler.EntityListHandler;
import cn.hutool.db.sql.SqlExecutor;
import com.luqili.utils.pub.link.Pair;
import com.luqili.utils.pub.rest.param.LuBasePageParams;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.*;

/**
 * SQL 自动分页工具
 */
public class LuAutoPageUtils {
    static final Logger logger = LoggerFactory.getLogger(LuAutoPageUtils.class);
    static final String[] WHERE_EXC_FIELDS = {"order", "sort", "page", "limit", "pageSize", "current"};

    /**
     * 根据实体Bean自动分页查询
     *
     * @param dbBean
     * @param pageParams
     * @param params
     * @return
     */
    public static LuAutoPageResult autoPage(Class dbBean, LuBasePageParams pageParams, LuAutoPageParams params) {
        String extWhereSql = params.getExtWhereSql();
        List<Object> extWhereParams = params.getExtWhereParams();
        boolean extWhereBefore = params.isExtWhereBefore();

        if (pageParams.getPageSize() == Integer.MAX_VALUE) {
            //分页为最大数时，没有必要再统计数量了，以反馈的结果集大小为准
            params.setEnableTotal(false);
        }
        //SQL 查询字段
        StringBuilder sqlFields = new StringBuilder();
        //SQL 表
        String tableName = StrUtil.toUnderlineCase(dbBean.getSimpleName());
        //SQL 条件查询字段
        StringBuilder sqlWhere = new StringBuilder();
        String sortField = null;

        Field[] fields = ClassUtil.getDeclaredFields(dbBean);
        List<Object> sqlParams = new ArrayList<>();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            String fieldName = field.getName();
            String fieldSQL = StrUtil.toUnderlineCase(fieldName);
            Object paramVal = pageParams.get(fieldName);
            if (paramVal != null && paramVal instanceof String && StringUtils.isBlank(paramVal.toString())) {
                //排除空字符串的情况
                pageParams.remove(fieldName);
            }
            if (params.getExcludeSelectFields() == null || (!params.getExcludeSelectFields().contains(fieldName)) && !params.getExcludeSelectFields().contains(fieldSQL)) {
                if (sqlFields.length() > 0) {
                    sqlFields.append(",");
                }
                sqlFields.append(fieldSQL);
            }

            if (fieldName.equals(params.getDateRangeField())) {
                Pair<Date, Date> pair = pageParams.getDateRange(params.getDateRangeField(), params.getDateRangeBeforeDay(), params.getDateRangeMaxDay());
                sqlWhere.append("and ").append(fieldSQL).append(" between ? and ?");
                sqlParams.add(pair.getKey());
                sqlParams.add(pair.getValue());
            } else if (pageParams.containsKey(fieldName) && !ArrayUtil.contains(WHERE_EXC_FIELDS, fieldName)) {
                Class fieldType = field.getType();
                Object val = pageParams.get(fieldName);
                if (val instanceof Collection) {
                    Collection<Object> os = (Collection<Object>) val;
                    if (os.isEmpty()) {
                        //确定查询值一定为空的情况
                        LuAutoPageResult result = new LuAutoPageResult();
                        result.setTotal(0);
                        result.setData(new ArrayList<>());
                        return result;
                    } else if (os.size() == 1) {
                        //单一条件，优化为 =
                        val = os.iterator().next();
                        pageParams.put(fieldName, val);
                    }
                }
                if (val instanceof Collection) {
                    //支持IN的情况
                    sqlWhere.append("and ").append(fieldSQL).append(" in ( ");
                    Collection<Object> lv = (Collection<Object>) val;
                    boolean isFirst = true;
                    for (Object o : lv) {
                        if (!isFirst) {
                            sqlWhere.append(",");
                        }
                        isFirst = false;
                        sqlWhere.append("?");
                        sqlParams.add(o);
                    }
                    sqlWhere.append(")");
                } else if (fieldType == Byte.class || fieldType == Short.class || fieldType == Integer.class || fieldType == Long.class || fieldType == Date.class) {
                    sqlWhere.append("and ").append(fieldSQL).append(" = ? ");
                    sqlParams.add(MapUtil.get(pageParams, fieldName, fieldType));
                } else if (fieldType == String.class) {
                    sqlWhere.append("and ").append(fieldSQL).append(" like ? ");
                    sqlParams.add(getSearchLikeVal(MapUtil.getStr(pageParams, fieldName)));
                } else {
                    throw new LuPageException("不支持的字段类型：" + fieldName);
                }
            }

            //排序处理
            if (StringUtils.isBlank(params.getDefaultSortField())) {
                if (StrUtil.equalsAny(fieldName, "sort", "id", "createTime", "upTime", "apply_time")) {
                    params.setDefaultSortField(fieldSQL);
                }
            }

            if (pageParams.getSortKey().equals(fieldName)) {
                sortField = fieldSQL;
            }

        }
        if (params.getSearchKeyParams() != null) {
            LuAutoPageSearchKeyParams sk = params.getSearchKeyParams();
            String val = getSearchLikeVal(sk.getSearchKey());
            sqlWhere.append(" and (");
            for (int i = 0; i < sk.getSearchField().size(); i++) {
                String fieldSQL = StrUtil.toUnderlineCase(sk.getSearchField().get(i));
                if (i != 0) {
                    sqlWhere.append(" or ");
                }
                sqlWhere.append(fieldSQL).append(" like ?");
                sqlParams.add(val);
            }
            sqlWhere.append(" )");
        }

        if (StrUtil.isNotBlank(extWhereSql)) {
            extWhereSql = extWhereSql.trim();
            extWhereSql = StrUtil.removePrefix(extWhereSql, "and ");
            extWhereSql = StrUtil.removeSuffix(extWhereSql, " and");
            if (extWhereBefore) {
                sqlWhere.insert(0, " ");
                sqlWhere.insert(0, extWhereSql);
                if (extWhereParams != null && !extWhereParams.isEmpty()) {
                    sqlParams.addAll(0, extWhereParams);
                }
            } else {
                sqlWhere.append(" and ").append(extWhereSql);
                if (extWhereParams != null && !extWhereParams.isEmpty()) {
                    sqlParams.addAll(extWhereParams);
                }
            }
        }
        String sqlWhereStr = sqlWhere.toString().trim();
        sqlWhereStr = StrUtil.removePrefixIgnoreCase(sqlWhereStr, "and ");
        sqlWhereStr = StrUtil.removePrefixIgnoreCase(sqlWhereStr, " and");

        LuAutoPageResult result = new LuAutoPageResult();
        StringBuilder selectSQL = new StringBuilder();


        selectSQL.append("select ").append(sqlFields).append(" from ").append(tableName);
        if (sqlWhereStr.length() > 0) {
            selectSQL.append(" where ").append(sqlWhereStr);
        }

        selectSQL.append(" order by ");
        if (StrUtil.isNotBlank(sortField) && !sortField.equals(params.getDefaultSortField())) {
            selectSQL.append(sortField).append(" ").append(pageParams.getSortType()).append(" ,");
        }
        selectSQL.append(params.getDefaultSortField()).append(" ").append(pageParams.getSortType());

        try (Connection ct = params.getDataSource().getConnection()) {
            if (logger.isDebugEnabled()) {
                logger.debug("AUTO_PAGE_SELECT_SQL:", selectSQL, sqlParams);
            }
            List<Object> selectSQLParams = new ArrayList<>();
            selectSQLParams.addAll(sqlParams);
            Integer pageSize = pageParams.getPageSize();
            Integer current = pageParams.getCurrent();
            if (current == null || current < 1) {
                current = 1;
            }
            if (pageSize == null || pageSize < 1) {
                pageSize = 5;
            }
            if (pageSize != Integer.MAX_VALUE) {
                selectSQL.append(" limit ? , ? ");
                selectSQLParams.add((current - 1) * pageSize);
                selectSQLParams.add(pageSize);
            }


            List<Entity> queryDatas = SqlExecutor.query(ct, selectSQL.toString(), new EntityListHandler(), ArrayUtil.toArray(selectSQLParams, Object.class));
            List<Map<String, Object>> data = new ArrayList<>();
            for (Entity entity : queryDatas) {
                Map<String, Object> one = new HashMap<>();
                entity.forEach((k, v) -> {
                    one.put(StrUtil.toCamelCase(k), v);
                });
                if (params.getLuAutoPageHandOne() != null) {
                    params.getLuAutoPageHandOne().handOne(one);
                }
                data.add(one);
            }
            result.setData(data);

            if (params.isEnableTotal()) {
                StringBuilder coutSQL = new StringBuilder();
                coutSQL.append("select ");
                if (StringUtils.isNotBlank(params.getExtCountSql())) {
                    coutSQL.append(params.getExtCountSql());
                } else {
                    coutSQL.append(" count(*) as count ");
                }
                coutSQL.append(" from ").append(tableName);
                if (sqlWhereStr.length() > 0) {
                    coutSQL.append(" where ").append(sqlWhereStr);
                }
                if (logger.isDebugEnabled()) {
                    logger.debug("AUTO_PAGE_COUNT_SQL:", coutSQL, sqlParams);
                }
                Entity queryCount = SqlExecutor.query(ct, coutSQL.toString(), new EntityHandler(), ArrayUtil.toArray(sqlParams, Object.class));
                if (queryCount != null) {
                    if (StringUtils.isNotBlank(params.getExtCountSql())) {
                        Integer total = queryCount.getInt("count");
                        result.setTotal(total != null ? total : 0);
                        Map<String, Object> totalRow = new HashMap<>();
                        queryCount.forEach((k, v) -> {
                            totalRow.put(StrUtil.toCamelCase(k), v);
                        });
                        result.setTotalRow(totalRow);
                    } else {
                        Integer total = queryCount.getInt("count");
                        result.setTotal(total != null ? total : 0);
                    }
                }

            } else {
                result.setTotal(result.getData().size());
            }

        } catch (Exception e) {
            throw new LuPageException("SQL查询异常", e);
        }
        return result;
    }


    private static String getSearchLikeVal(String val) {
        val = val.trim();
        if (!StrUtil.startWith(val, "%")) {
            val = "%" + val;
        }
        if (!StrUtil.endWith(val, "%")) {
            val = val + "%";
        }
        return val;
    }
}
